﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Borton.Beans;

namespace Borton.Data
{
    class DataAccess
    {

        public static void load_(Bean bean)
        {
            String query = "select " + getSerializedColumnNames(bean, false) + " from [" + bean.getTableName() + "] where " + bean.getFields()[0].columnName + " = " + bean.getFields()[0].getValueForSave();
            Console.WriteLine(query);
            OleDbDataReader dr = new OleDbCommand(query, ConnectionAdapter.getOpenConnection()).ExecuteReader();
            dr.Read();
            for (int i = 0; i < bean.getFields().Length; i++)
            {
                bean.getFields()[i].setValue((String)dr.GetValue(i).ToString());
            }
            ConnectionAdapter.closeConnection();
        }

        public static Bean[] getAll__(Bean bean)
        {
            String query = "select " + getSerializedColumnNames(bean, false) + " from [" + bean.getTableName() + "];";
            Console.WriteLine(query);
            OleDbDataReader dr = new OleDbCommand(query, ConnectionAdapter.getOpenConnection()).ExecuteReader();
            List<Bean> beans = new List<Bean>();
            
            while (dr.Read()) {
                bean = (Bean)bean.Clone();
                for (int i = 0; i < bean.getFields().Length; i++)
                {
                    bean.getFields()[i].setValue(dr.GetValue(i).ToString());
                }
                beans.Add(bean);
            }
            ConnectionAdapter.closeConnection();
            return beans.ToArray();
        }

        public static void save_(Bean bean) {
            String query;
            
            if (bean.isNew()) {
                query = "insert into [" + bean.getTableName() + "] (" + getSerializedColumnNames(bean, true) + ") values (" + getSerializedFieldValues(bean, true) + ")";
            } else {
                query = "update [" + bean.getTableName() + "] set " + getSerializedKeyValueFields(bean, null) + " where " + bean.getId().columnName + " = " + bean.getFields()[0].getValueForSave();
            }
            Console.WriteLine(query);
            new OleDbCommand(query, ConnectionAdapter.getOpenConnection()).ExecuteReader();
            
            if (bean.isNew()) {
                query = "select max(" + bean.getId().columnName + ") from [" + bean.getTableName() + "] where " + getSerializedKeyValueFields(bean, " and ");
                OleDbDataReader dr = new OleDbCommand(query, ConnectionAdapter.getOpenConnection()).ExecuteReader();
                dr.Read();
                bean.getId().setValue(dr.GetValue(0).ToString());
            }
            ConnectionAdapter.closeConnection();
        }

        public static void delete_(Bean bean)
        {
            String query = "delete from " + bean.getTableName() + " where " + bean.getId().columnName + " = " + bean.getId().getValueForSave();
            Console.WriteLine(query);
            new OleDbCommand(query, ConnectionAdapter.getOpenConnection()).ExecuteReader();
            ConnectionAdapter.closeConnection();
        }

        public static String getSerializedColumnNames(Bean bean, bool withoutId)
        {
            String res = "";
            Field[] fields = bean.getFields();
            for (int i = 0; i < fields.Length-1; i++)
            {
                if (!withoutId || !fields[i].Equals(bean.getId()))
                {
                    res += fields[i].columnName + ", ";
                }
            }
            res += fields[fields.Length - 1].columnName;
            return res;
        }

        public static String getSerializedFieldValues(Bean b, bool withoutId)
        {
            String res = "";
            Field[] fields = b.getFields();
            for (int i = 0; i < fields.Length - 1; i++)
            {
                if (!withoutId || !fields[i].Equals(b.getId()))
                {
                    res += "" + getFieldValueForDb(fields[i]) + ", ";
                }
            }
            res += "" + getFieldValueForDb(fields[fields.Length - 1]) + "";
            return res;
        }

        public static String getSerializedKeyValueFields(Bean bean, String separator) // without id
        {
            String res = "";
            if (separator == null)
            {
                separator = ", ";
            }
            Field[] fields = bean.getFields();
            for (int i = 1; i < fields.Length - 1; i++)
            {
                if (!bean.getId().columnName.Equals(fields[i].columnName))
                {
                    res += fields[i].columnName + "=" + getFieldValueForDb(fields[i]) + separator;
                }
            }
            res += fields[fields.Length - 1].columnName + "=" + getFieldValueForDb(fields[fields.Length - 1]) + "";
            return res;
        }

        private static String getFieldValueForDb(Field field)
        {
            switch (field.type)
            {
                case FieldType.STRING:
                    return "'" + field.getValueForSave() + "'";
                default:
                    return field.getValueForSave();
            }
        }

        public static bool login(String username, String password)
        {
            bool ans = false;
            String query = "select jelszo from [Felhasznalo] where nev = '" + username + "'";
            Console.WriteLine(query);
            OleDbDataReader dr = new OleDbCommand(query, ConnectionAdapter.getOpenConnection()).ExecuteReader();
            if (dr.Read())
            {
                if (dr.GetString(0).Equals(password))
                {
                    ans = true;
                }
            }
            ConnectionAdapter.closeConnection();
            return ans;
        } 

    }
}
